﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WinStudentMS
{
    public partial class StudentListForm : Form
    {
        public StudentListForm()
        {
            InitializeComponent();
        }

        private Action reLoad = null;

        //单例
        private static StudentListForm studentListForm = null;
        public static StudentListForm CreateInstance()
        {
            if (studentListForm == null || studentListForm.IsDisposed)
                studentListForm = new StudentListForm();
            return studentListForm;
        }

        private void StudentListForm_Load(object sender, EventArgs e)
        {
            LoadClasses();
            LoadAllStudentList();
        }

        private void LoadAllStudentList()
        {
            string sql = "select StuId,StuName,ClassName,GradeName,Sex,Phone from StudentInfo s " +
                "inner join ClassInfo c on c.ClassId=s.ClassId " +
                "inner join GradeInfo g on g.GradeId=c.GradeId where s.IsDeleted=0";
            DataTable dataTable = SqlHelper.GetDataTable(sql);
            if (dataTable.Rows.Count > 0)
            {
                foreach (DataRow row in dataTable.Rows)
                {
                    string className = row["ClassName"].ToString();
                    string gradeName = row["GradeName"].ToString();
                    row["ClassName"] = className + "--" + gradeName;
                }
            }
            //多余列不显示
            /*dgvStudents.AutoGenerateColumns = false;
            dgvStudents.Columns.Remove(dgvStudents.Columns[3]);*/
            dgvStudents.DataSource = dataTable;
        }

        private void LoadClasses()
        {
            string sql = "select ClassId,ClassName,GradeName from ClassInfo c," +
                "GradeInfo g where c.GradeId=g.GradeId";
            DataTable dataTable = SqlHelper.GetDataTable(sql);
            if (dataTable.Rows.Count > 0)
            {
                foreach (DataRow row in dataTable.Rows)
                {
                    string ClassName = row["ClassName"].ToString();
                    string gradeName = row["GradeName"].ToString();
                    row["ClassName"] = ClassName + "--" + gradeName;
                }
            }
            DataRow dataRow = dataTable.NewRow();
            dataRow["ClassId"] = 0;
            dataRow["ClassName"] = "请选择";
            dataTable.Rows.InsertAt(dataRow, 0);
            cboClasses.DataSource = dataTable;
            cboClasses.DisplayMember = "ClassName";
            cboClasses.ValueMember = "ClassId";
        }

        private void btnFind_Click(object sender, EventArgs e)
        {
            int classId = (int)cboClasses.SelectedValue;
            string stuName = txtStuName.Text.Trim();
            string sql = "select StuId,StuName,ClassName,GradeName,Sex,Phone from StudentInfo s " +
                "inner join ClassInfo c on c.ClassId=s.ClassId " +
                "inner join GradeInfo g on g.GradeId=c.GradeId ";
            sql += "where 1=1";
            if (classId > 0)
            {
                sql += " and s.ClassId=@ClassId";
            }
            if (string.IsNullOrEmpty(stuName))
            {
                sql += " and StuName like @StuName";
            }
            sql += " and s.IsDeleted=0 order by StuId";
            SqlParameter[] paras =
            {
                new SqlParameter("@ClassId", classId),
                new SqlParameter("@StuName", "%" + stuName + "%")
            };
            DataTable dataTable = SqlHelper.GetDataTable(sql, paras);
            if (dataTable.Rows.Count > 0)
            {
                foreach (DataRow row in dataTable.Rows)
                {
                    string ClassName = row["ClassName"].ToString();
                    string gradeName = row["GradeName"].ToString();
                    row["ClassName"] = ClassName + "--" + gradeName;
                }
            }
            cboClasses.DataSource = dataTable;
        }

        private void dgvStudents_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex != -1)
            {
                DataRow dataRow = (dgvStudents.Rows[e.RowIndex].DataBoundItem as DataRowView).Row;
                int stuId = int.Parse(dataRow["StuId"].ToString());
                DataGridViewCell cell = dgvStudents.Rows[e.RowIndex].Cells[e.ColumnIndex];
                if (cell is DataGridViewLinkCell && cell.FormattedValue.ToString() == "修改")
                {
                    reLoad = LoadAllStudentList;
                    EditStudentForm editStudentForm = new EditStudentForm();
                    editStudentForm.MdiParent = this.MdiParent;
                    //传值
                    editStudentForm.Tag = new TagObject()
                    {
                        EditId = stuId,
                        ReLoad = reLoad
                    };
                    editStudentForm.Show();
                }
                else if (cell is DataGridViewLinkCell && cell.FormattedValue.ToString() == "删除")
                {
                    if (MessageBox.Show("您确定要删除该学生信息吗？", "删除学生提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                    {
                        //假删除
                        string sqlDelFalse = "update StudentInfo set IsDeleted=1 where StuId=@StuId";
                        SqlParameter para = new SqlParameter("@StuId", stuId);
                        int count = SqlHelper.ExecuteNonQuery(sqlDelFalse, para);
                        if (count > 0)
                        {
                            MessageBox.Show($"该学生删除成功!", "删除学生提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            DataTable dataTable = (DataTable)dgvStudents.DataSource;
                            dataTable.Rows.Remove(dataRow);
                            dgvStudents.DataSource = dataTable;
                        }
                        else
                        {
                            MessageBox.Show($"该学生删除失败!", "删除学生提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            return;
                        }
                        //真删除
                    }
                }
            }
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            List<int> listId = new List<int>();
            for (int i = 0; i < dgvStudents.Rows.Count; i++)
            {
                DataGridViewCheckBoxCell cell = dgvStudents.Rows[i].Cells["colCheck"] as DataGridViewCheckBoxCell;
                bool check = Convert.ToBoolean(cell.Value);
                if (check)
                {
                    DataRow dr = (dgvStudents.Rows[i].DataBoundItem as DataRowView).Row;
                    int stuId = (int)dr["StuId"];
                    listId.Add(stuId);
                }
            }
            if (listId.Count == 0)
            {
                MessageBox.Show("请选择删除的学生信息!", "删除学生提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            else
            {
                if(MessageBox.Show("您确定要删除该学生的信息吗？", "删除学生提示", MessageBoxButtons.YesNo, 
                    MessageBoxIcon.Question) == DialogResult.Yes)
                {

                    int count = 0;
                    //启动事务
                    using (SqlConnection connection = new SqlConnection(SqlHelper.connString))
                    {
                        connection.Open();
                        SqlTransaction transaction = connection.BeginTransaction();
                        SqlCommand command = new SqlCommand();
                        command.Transaction = transaction;

                        try
                        {
                            foreach (int id in listId)
                            {
                                command.CommandText = "delete from StudentInfo where StuId=@StuId";
                                SqlParameter para = new SqlParameter("@StuId", id);
                                command.Parameters.Clear();
                                command.Parameters.Add(para);
                                count += command.ExecuteNonQuery();
                            }
                            transaction.Commit();
                        }
                        catch (SqlException exception)
                        {
                            transaction.Rollback();
                            MessageBox.Show("删除学生出现异常!", "删除学生提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            return;
                        }
                    }
                    if (count == listId.Count)
                    {
                        MessageBox.Show("这些学生信息删除成功!", "删除学生提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        DataTable dataTable = (DataTable)dgvStudents.DataSource;
                        string idStr = string.Join(",", listId);
                        DataRow[] dataRow = dataTable.Select("StuId in(" + idStr + ")");
                        foreach (DataRow row in dataRow)
                        {
                            dataTable.Rows.Remove(row);
                        }
                        dgvStudents.DataSource = dataTable;
                    }
                }
            }
        }
    }
}
